/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cxhd.lis.ctrl;

import com.cxhd.lis.model.newmodel.LisPatientInfo;
import com.cxhd.lis.model.newmodel.LisSamplecollection;
import com.cxhd.lis.model.newmodel.LisSqd;
import com.cxhd.lis.model.newmodel.LisUser;
import com.cxhd.lis.model.newmodel.lisTestResult;
import com.google.common.reflect.TypeToken;
import com.google.gson.Gson;
import com.jfinal.aop.Before;
import com.jfinal.core.Controller;
import com.jfinal.ext.route.ControllerBind;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.lang3.StringUtils;

/**
 *
 * @author 52461
 */
@ControllerBind(controllerKey = "/view/select")
public class SlectCtrl extends Controller {

    public void index() {
        //render("/view/select.jsp");
        render("/view/queryInfo.jsp");
    }

    public void getList() {
        String sql = "select DISTINCT a.UUID,a.SAMPLENO,a.MEASURETIME,a.DEVICEID,b.PK,c.PK as brpk,b.CHECKOPERATOR,b.RESULTSTATUS,c.PATIENTID,c.PATIENTNAME,c.SEX,b.SAMPLE,b.EXAMINAIM,"
                + "b.RECEIVER,c.AGE,b.SECTION,b.BED_NO,b.STAYHOSPITALMODE,b.PRINTTIME,b.SJYS,b.JYYS ";
        String param = " from lis_testresult a inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  GROUP BY a.UUID ORDER by a.MEASURETIME desc";
        if (getPara("searchContent") != null) {
            param = " from lis_testresult a inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  "
                    + " where c.PATIENTID='" + getPara("searchContent") + "' OR c.PATIENTNAME LIKE '%" + getPara("searchContent") + "%' GROUP BY a.UUID ORDER by a.MEASURETIME desc";
        }
        System.out.println(sql + " " + param);
        Page<Record> userPage = Db.use("mysql").paginate(getParaToInt("currentPage", 1),
                getParaToInt("pageSize", 10), sql, param);
        renderJson(userPage);
    }

    public void getType() {
        renderJson(Db.use("mysql").find("SELECT DEVICEMODEL,DEVICENAME from lis_deviceset"));
    }
//     public void selectMatch(){
//         String content=getPara("content");
//         String sql="select DISTINCT a.UUID,a.SAMPLENO,a.MEASURETIME,a.DEVICEID,b.PK,b.CHECKOPERATOR,b.RESULTSTATUS,c.PATIENTID,c.PATIENTNAME,c.SEX,b.SAMPLE,b.EXAMINAIM,"
//                 + "b.RECEIVER,c.AGE,b.SECTION,b.BED_NO,b.STAYHOSPITALMODE,b.PRINTTIME,b.SJYS,b.JYYS from lis_testresult a "
//                 + "inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  "
//                 + " where c.PATIENTID='"+content+"' OR c.PATIENTNAME LIKE '%"+content+"%'";
//         String allsql="select DISTINCT a.UUID,a.SAMPLENO,a.MEASURETIME,a.DEVICEID,b.PK,b.CHECKOPERATOR,b.RESULTSTATUS,c.PATIENTID,c.PATIENTNAME,c.SEX,b.SAMPLE,b.EXAMINAIM,"
//                 + "b.RECEIVER,c.AGE,b.SECTION,b.BED_NO,b.STAYHOSPITALMODE,b.PRINTTIME,b.SJYS,b.JYYS from lis_testresult a "
//                 + "inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  ";
//         if(content!=null){
//            renderJson(Db.use("mysql").find(sql));
//         }else{
//            renderJson(Db.use("mysql").find(allsql));
//         }
//     }

    public void changeType() {
        String date = getPara("date");
        String type = getPara("type");
        String sql = "select DISTINCT a.UUID,a.SAMPLENO,a.MEASURETIME,a.DEVICEID,b.PK,b.CHECKOPERATOR,b.RESULTSTATUS,c.PATIENTID,c.PATIENTNAME,c.SEX,b.SAMPLE,b.EXAMINAIM,"
                + "b.RECEIVER,c.AGE,b.SECTION,b.BED_NO,b.STAYHOSPITALMODE,b.PRINTTIME,b.SJYS,b.JYYS ";
        String param = "";
        if (date != null && type == null) {
            param = "from lis_testresult a inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  "
                    + " where a.MEASURETIME BETWEEN STR_TO_DATE('" + date + " 00:00:00','%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('" + date + " 23:59:59','%Y-%m-%d %H:%i:%s') ";
        } else if (date == null && type != null) {
            param = "from lis_testresult a inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  "
                    + " where a.DEVICEID='" + type + "'";
        } else if (date != null && type != null) {
            param = "from lis_testresult a inner JOIN lis_samplecollection b on a.UUID=b.UUID inner  JOIN lis_patient c on b.INFOPK=c.PK  "
                    + " where a.MEASURETIME BETWEEN STR_TO_DATE('" + date + " 00:00:00','%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('" + date + " 23:59:59','%Y-%m-%d %H:%i:%s') and "
                    + "a.DEVICEID='" + getPara("type") + "'";
        }

        Page<Record> userPage = Db.use("mysql").paginate(getParaToInt("currentPage", 1),
                getParaToInt("pageSize", 10), sql, param);
        renderJson(userPage);
//         if(date!=null && type==null){
//             renderJson(Db.use("mysql").find(datesql));
//         }else if(date==null && type!=null){
//             renderJson(Db.use("mysql").find(typesql));
//         }else if(date!=null && type!=null){
//             renderJson(Db.use("mysql").find(sql));
//         }

    }


     
     public void getPaixu() {
        String sql = "select TESTPK,DATA1 from pp_device_test a LEFT JOIN lis_deviceset b on a.DEVICEPK=b.PK where b.DEVICEMODEL='" + getPara("devi") + "'";
        renderJson(Db.find(sql));
    }
     
     
    public void getResult(){
//        String sql="select a.PK,a.TESTRESULT,a.TESTID,a.ITEMPK,a.DEVICEID,a.UUID,b.CHINESENAME,a.UNIT,a.TESTSTATUS,a.SAMPLETYPE,a.FEE,"

//                + "a.MEASURETIME,c.MREFLO0,c.MREFHI0,c.Decimalplace from lis_testresult a LEFT JOIN lis_testdescribe b on a.ITEMPK=b.PK "
//                + "LEFT JOIN lis_referencevalue c ON a.ITEMPK=c.PK  "
//                + "where a.UUID='"+getPara("UUID")+"' order by b.PRINTORD" ;

//          String sql="select a.PK,a.RESULTFLAG,a.TESTRESULT,a.TESTID,a.ITEMPK,a.DEVICEID,a.UUID,b.CHINESENAME,a.UNIT,a.TESTSTATUS,a.SAMPLETYPE," +
//                        "a.MEASURETIME,c.MREFLO0,c.MREFHI0,c.Decimalplace " +
//                        "from lis_testresult a LEFT JOIN lis_testdescribe b on a.ITEMPK=b.PK LEFT JOIN lis_referencevalue c ON a.ITEMPK=c.PK  " +
//                        "LEFT JOIN lis_samplecollection d ON a.UUID=d.UUID  " +
//                        "where d.INFOPK="+getParaToInt("brpk")+" order by b.PRINTORD";

// old version
        String sql = "select a.PK,a.RESULTFLAG,a.TESTRESULT,a.TESTID,a.ITEMPK,a.DEVICEID,a.UUID,b.CHINESENAME,a.UNIT,a.TESTSTATUS,a.SAMPLETYPE,a.FEE,"
                + "a.MEASURETIME,c.MREFLO0,c.MREFHI0,c.Decimalplace from lis_testresult a LEFT JOIN lis_testdescribe b on a.ITEMPK=b.PK "
                + "LEFT JOIN lis_referencevalue c ON b.TESTID=c.TESTID  "
                + "where a.UUID='" + getPara("UUID") + "'";

//   String sql = "select a.PK,a.RESULTFLAG,a.TESTRESULT,a.TESTID,a.ITEMPK,a.DEVICEID,a.UUID,b.CHINESENAME,a.UNIT,a.TESTSTATUS,a.SAMPLETYPE,a.FEE,"
//                + "a.MEASURETIME,c.MREFLO0,c.MREFHI0,c.Decimalplace from lis_testresult a LEFT JOIN lis_testdescribe b on a.ITEMPK=b.PK "
//                + "LEFT JOIN pp_device_test c ON a.TESTID=c.TESTID  "
//                + "where a.DEVICEID=c.DEVICEID and (a.UUID='" + getPara("UUID") + "' or PARENT_UUID='" + getPara("UUID") + "')";
        System.out.println(sql);
        renderJson(Db.use("mysql").find(sql));

//          String sql="select a.PK,a.TESTRESULT,a.TESTID,a.ITEMPK,a.DEVICEID,a.UUID,b.CHINESENAME,a.UNIT,a.TESTSTATUS,a.SAMPLETYPE," +
//                        "a.MEASURETIME,c.MREFLO0,c.MREFHI0,c.Decimalplace " +
//                        "from lis_testresult a LEFT JOIN lis_testdescribe b on a.ITEMPK=b.PK LEFT JOIN lis_referencevalue c ON a.ITEMPK=c.PK  " +
//                        "LEFT JOIN lis_samplecollection d ON a.UUID=d.UUID  " +
//                        "where d.INFOPK="+getParaToInt("brpk")+" order by b.PRINTORD";
        
    }

    public void getBrInfo() {
        Record d = Db.use("mysql").findFirst("select * from lis_sqd where patientid='" + getPara("brid") + "'");
        if (d != null) {
            renderJson(d);
        } else {
            renderText("false");
        }
    }

    public void printReport() {
        try {
            new LisSamplecollection().updateDY(this);
            renderText("true");
        } catch (Exception ex) {
            ex.printStackTrace();
            renderText("false");
        }
    }

    public void queryList() {

        String sql = "select b.*,c.PATIENTID,c.PATIENTNAME,c.SEX,c.AGE,b.UUID,a.DEVICEID,a.MEASURETIME ";
        //String param = " from lis_samplecollection a left join lis_patient b on a.INFOPK=b.pk left join (select UUID,DEVICEID from lis_testresult group by UUID) c ON a.UUID=c.UUID";
        String table = " from lis_testresult a inner JOIN "
                + "lis_samplecollection b on a.UUID=b.UUID inner  JOIN "
                + "lis_patient c on b.INFOPK=c.PK  ";
        String where = "";
        String searchp = "";
        String searchDate = "";
        String project = "";
        String deviceName = "";
        if (!StringUtils.isBlank(getPara("searchContent"))) {
            searchp = " (c.PATIENTID='" + getPara("searchContent") + "' OR c.PATIENTNAME ='" + getPara("searchContent") + "')";
            where += searchp;
        }
        if (!StringUtils.isBlank(getPara("device"))) {
            deviceName = StringUtils.isBlank(where) ? "" : " and ";
            deviceName = " a.DEVICEID='" + getPara("device") + "'";
            where += deviceName;
        }
        if (!StringUtils.isBlank(getPara("startDate")) && !StringUtils.isBlank(getPara("endDate"))) {

            searchDate = StringUtils.isBlank(where) ? "" : " and ";
            searchDate += " a.MEASURETIME BETWEEN '" + getPara("startDate") + "' and '" + getPara("endDate") + "'";
            where += searchDate;
        }

        if (!StringUtils.isBlank(getPara("proName"))) {
            project = StringUtils.isBlank(where) ? "" : " and ";
            project += " a.TESTID='" + getPara("proName") + "'";
            where += project;
        }

        if (!StringUtils.isBlank(where)) {
            where = " where" + where;
        }

        String end = " GROUP BY a.UUID ORDER by a.MEASURETIME desc";
        String param = table + where + end;
        System.out.println(sql + " " + param);
        Page<Record> userPage = Db.use("mysql").paginate(getParaToInt("currentPage", 1),
                getParaToInt("pageSize", 10), sql, param);
        renderJson(userPage);
    }

}
